/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eschool.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;

import com.eschool.model.Branch;
import com.eschool.model.Section;
import com.eschool.model.Student;
import com.eschool.util.DateFormaterUtil;
import com.eschool.util.HibernateUtil;
import com.eschool.util.JdbcUtil;

/**
 * 
 * @author Ammar
 */

public class StudentDao {

	public static boolean addStudent(Student student) {

		boolean isInserted = false;

		Connection connection = JdbcUtil.openConnection();

		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call InsertStudent(?,?,?,?,?,?) }");

			storedProc.setString(1, student.getName());
			storedProc.setString(
					2,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,
							student.getDob()));
			storedProc.setString(3, student.getPresentAddress());
			storedProc.setBoolean(4, student.getCurrentStatus());
			storedProc.setInt(5, student.getParent().getParentId());
			storedProc.setInt(6, student.getSection().getSectionId());

			storedProc.execute();

			isInserted = true;
			connection.close();
		}

		catch (Exception e) {
			isInserted = false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isInserted;
		}
	}

	public static Student getStudentByProperty(String property, Object value) {

		Session session = HibernateUtil.currentSession();

		Criteria crit = session.createCriteria(Student.class);
		crit.add(Restrictions.eq(property, value));

		Student student = (Student) crit.uniqueResult();

		HibernateUtil.closeSession(session);

		return student;
	}

	public static List getStudents(int branchId) {

		Session session = HibernateUtil.currentSession();
		Criteria crit = session.createCriteria(Student.class);

		crit.createAlias("section", "s").createAlias("s.clas", "c").createAlias("c.branch", "b");
		
		crit.add(Restrictions.eq("b.branchId",branchId));

		List students = (List<Student>) crit.list();

		HibernateUtil.closeSession(session);

		if (students == null)
			return Collections.EMPTY_LIST;

		return students;
	}
	
	public static List getStudents(Map<String, Object> conditions) {

		  Session session = HibernateUtil.currentSession();
		  Criteria crit = session.createCriteria(Student.class);

		  if (conditions != null)

		   for (Iterator iterator = conditions.entrySet().iterator(); iterator
		     .hasNext();) {

		    Map.Entry condition = (Map.Entry) iterator.next();

		    crit.add(Restrictions.eq(((String) condition.getKey()),
		      condition.getValue()));
		   }

		  List students = (List<Student>) crit.list();

		  HibernateUtil.closeSession(session);

		  if (students == null)
		   return Collections.EMPTY_LIST;

		  return students;
		 }

	public static boolean updateStudent(Student student) {

		boolean isUpdated = false;

		Connection connection = JdbcUtil.openConnection();

		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call spStudentUpdate(?,?,?,?,?,?,?) }");

			storedProc.setString(1, student.getName());
			storedProc.setString(
					2,
					DateFormaterUtil.format(DateFormaterUtil.SQL_DATE,
							student.getDob()));
			storedProc.setString(3, student.getPresentAddress());
			storedProc.setBoolean(4, student.getCurrentStatus());
			storedProc.setInt(5, student.getParent().getParentId());
			storedProc.setInt(6, student.getSection().getSectionId());
			storedProc.setInt(7, student.getStudentId());

			storedProc.execute();

			isUpdated = true;
			connection.close();
		}

		catch (Exception e) {
			isUpdated = false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isUpdated;
		}
	}

	public static List getStudentsByBranch(Branch branch) {

		Session session = HibernateUtil.currentSession();
		// Criteria crit = session.createCriteria(Student.class);

		Criteria crit = session.createCriteria(Student.class, "student");
		crit.add(Restrictions
				.sqlRestriction("this_.SectionID IN (SELECT s.SectionID from tblSection s WHERE s.ClassID IN "
						+ "(SELECT c.ClassID from tblClass c WHERE c.BranchID="
						+ branch.getBranchId() + "))"));

		List students = (List<Student>) crit.list();

		HibernateUtil.closeSession(session);

		return students;
	}

	public static boolean promoteStudent(Student student, Section newClass) {

		boolean isUpdated = false;

		Connection connection = JdbcUtil.openConnection();

		try {
			CallableStatement storedProc = connection
					.prepareCall("{ call spPromoteStudent(?,?,?) }");

			storedProc.setInt(1, student.getSection().getSectionId());
			storedProc.setInt(2, newClass.getSectionId());
			storedProc.setInt(3, student.getStudentId());

			storedProc.execute();

			isUpdated = true;
			connection.close();
		}

		catch (Exception e) {
			isUpdated = false;
			connection.close();
			e.printStackTrace();
		} finally {
			return isUpdated;
		}
	}
}
